package com.example.bestudentsystem.demos.web.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.bestudentsystem.demos.web.pojo.Student;
import com.example.bestudentsystem.demos.web.vo.SCVo;
import com.example.bestudentsystem.demos.web.vo.StudentVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {


    @Select("SELECT student.sno,student.sname,sc.cno,course.cname,sc.score,sc.attendance FROM student JOIN  sc ON student.sno=sc.sno\n" +
            "JOIN course ON sc.cno=course.cno WHERE  student.classid=(SELECT teacher.classid FROM teacher WHERE teacher.tno=#{tno})" +
            " AND student.sno LIKE CONCAT('%', #{sno}, '%')")
    IPage<SCVo> getStudentScoreByClass(IPage<SCVo> iPage, String tno, String sno);

    @Select("SELECT * FROM student WHERE student.classid=(SELECT teacher.classid FROM teacher WHERE teacher.tno=#{tno}) AND student.sname LIKE CONCAT('%',#{name},'%')")
    IPage<Student> getStudentDataByClass(IPage<Student> iPage,String tno,String name);

    @Select("SELECT student.sno,student.sname,sc.cno,course.cname,sc.score,sc.attendance,student.classid FROM student JOIN  sc ON student.sno=sc.sno\n" +
            "JOIN course ON sc.cno=course.cno WHERE  course.cno=#{cno}" +
            " AND student.sname LIKE CONCAT('%', #{name}, '%')")
    IPage<SCVo> getStudentScoreByCno(IPage<SCVo> iPage,String cno,String name);

    @Select("SELECT student.sno,student.sname,sc.cno,course.cname,sc.score,sc.attendance,course.photo FROM student JOIN  sc ON student.sno=sc.sno\n" +
            "JOIN course ON sc.cno=course.cno WHERE  student.sno=#{sno}")
    List<SCVo> getStudentSCBySno(String sno);

    @Update("UPDATE sc SET attendance=#{attendance} WHERE sno=#{sno} AND cno=#{cno}")
    void setAttendance(String sno,String cno,float attendance);
    //分页查找学生课程（学号、姓名、课程号、教师号、教师名）
    @Select("SELECT student.sno,student.sname,sc.cno,course.cname,course.tno,teacher.tname,student.classid,course.israte,sc.status FROM student JOIN  sc ON student.sno=sc.sno\n" +
            "JOIN course ON sc.cno=course.cno JOIN teacher ON course.tno=teacher.tno WHERE " +
            "student.sno LIKE CONCAT('%', #{sno}, '%') AND course.israte=1")
    IPage<SCVo> getStudentCourse(IPage<SCVo> iPage,String sno);
}
